package top.huhuiyu.study.javawebjdbc.dao;

import top.huhuiyu.study.javawebjdbc.entity.TbMovie;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 胡辉煜
 */
public class TbMovieDAO {

  public static int insert(TbMovie movie) throws Exception {
    Connection conn = DBHelper.getConnection();
    // 带参数的sql语句
    String sql = "insert into tb_movie(movie,info,score) values(?,?,?)";
    // 预处理语句
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数值
    ps.setString(1, movie.getMovie());
    ps.setString(2, movie.getInfo());
    ps.setInt(3, movie.getScore());
    // 执行sql语句并获取返回影响行数
    int result = ps.executeUpdate();
    conn.close();
    return result;
  }

  public static List<TbMovie> query() throws Exception {
    List<TbMovie> list = new ArrayList<>();
    Connection conn = DBHelper.getConnection();
    String sql = "select * from tb_movie";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 读取查询结果
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
      TbMovie movie = new TbMovie();
      movie.setId(rs.getInt("id"));
      movie.setMovie(rs.getString("movie"));
      movie.setInfo(rs.getString("info"));
      movie.setScore(rs.getInt("score"));
      movie.setCreated(rs.getTimestamp("created"));
      list.add(movie);
    }
    conn.close();
    return list;
  }

  public static int delete(Integer id) throws Exception {
    Connection conn = DBHelper.getConnection();
    String sql = "delete from tb_movie where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1,id);
    int i = ps.executeUpdate();
    conn.close();
    return i;
  }

}
